package case10

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"gorm.io/gorm/schema"
	"log"
	"os"
	"study_gorm_v2/src/case9"
	"time"
)

func Test() {

	fmt.Println("🎯示例十：一对多关系Preload预加载关联查询")

	db, err := mysqlConnect()
	if err != nil {
		fmt.Println(err)
		return
	}

	//// 自动创建表结构
	//if err := db.AutoMigrate(&UserCase10{}, &BandCase10{}); err != nil {
	//	fmt.Println("create fail: ", err)
	//	return
	//}
	//fmt.Println("创建成功...")

	//// 批量插入用户数据
	//ucArr1 := []UserCase10{
	//	UserCase10{UserName: "丽丽", Phone: "13520326021"},
	//	UserCase10{UserName: "张三", Phone: "13520326022"},
	//	UserCase10{UserName: "里斯", Phone: "13520326023"},
	//	UserCase10{UserName: "王五", Phone: "13520326024"},
	//	UserCase10{UserName: "李四", Phone: "13520326025"},
	//}
	//db.Create(&ucArr1)

	//// 插入银行账户数据
	//db.Create(&BandCase10{BankName: "招商银行", BankCode: "1232321", UserID: 1})
	//db.Create(&BandCase10{BankName: "民生银行", BankCode: "3212321", UserID: 1})
	//db.Create(&BandCase10{BankName: "浦发银行", BankCode: "4212321", UserID: 2})

	// 查询所有用户数据
	ucArr2 := []UserCase10{}
	db.Find(&ucArr2)
	for _, uc := range ucArr2 {
		fmt.Println(uc)
	}

	// 查询所有银行账户数据
	bArr1 := []BandCase10{}
	db.Find(&bArr1)
	for _, bc := range bArr1 {
		fmt.Println(bc)
	}

	var uc2 UserCase10
	var bc2 BandCase10
	//// 预加载关联数据
	//db.Preload(_Banks).Find(&uc2)

	// 预加载关联 查询用户id：2的用户数据和银行账户
	//db.Preload(_Banks).Find(&uc2, 2)

	// 子查询：查询银行账户UserId=2的数据
	//db.Where("user_id = (?)", db.Table("user_case10").Select("id").Where("id = ?", 2)).Find(&bc2)
	//SELECT * FROM `band_case10` WHERE user_id = (SELECT id FROM `user_case10` WHERE id = 2)

	// left join 关联查询
	db.Model(
		&BandCase10{}).Joins(
		"left join user_case10 on user_case10.id = band_case10.user_id").Where(
		"user_case10.id = ?", 2).Find(&bc2)
	//SELECT `band_case10`.`id`,`band_case10`.`bank_name`,`band_case10`.`bank_code`,`band_case10`.`user_id`
	//FROM `band_case10`
	//left join user_case10 on user_case10.id = band_case10.user_id
	//WHERE user_case10.id = 2

	fmt.Println(bc2)
	fmt.Println(uc2)
	fmt.Println(uc2.Banks)

	fmt.Println(db)

}

// 初始化连接
func mysqlConnect() (*gorm.DB, error) {
	url := "root:123456@tcp(127.0.0.1:3306)/case1?charset=utf8mb4&parseTime=True&loc=Local"

	// 配置自定义 Logger 打印sql日志
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 可以设置为不同级别 Silent-不打印任何日志 Error-只打印错误日志 Warn-打印慢查询和错误 Info-打印慢查询和错误
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
		Logger: newLogger, // 设置 Logger
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true, // 关闭默认，使用表名
		},
	})
	if err != nil {
		return nil, fmt.Errorf("mysql connect err: %v", err)
	}

	return db, nil
}

// 一对多关系结构体
// 用户结构体
type UserCase10 struct {
	ID              int          `gorm:"primarykey;autoIncrement"`
	UserName        string       `gorm:"type:varchar(20);not null;unique"`
	Phone           string       `gorm:"type:varchar(20);unique"`
	case9.BaseCase9              // 嵌入结构体
	Banks           []BandCase10 `gorm:"foreignKey:UserID"` // 一个用户对应多个银行账户
}

// 银行账户结构体
type BandCase10 struct {
	ID       int    `gorm:"primarykey;autoIncrement"`
	BankName string `gorm:"type:varchar(20);not null;"`
	BankCode string `gorm:"type:varchar(20);not null;"`
	UserID   int    `gorm:"index"` // 外键字段 建议添加索引
}

// 定义包级常量
const (
	_Banks = "Banks"
)
